This quickstart guide explains how to join two tables A and B using Jaccard similarity measure. First, you need to import the required packages as follows (if you have installed py_stringsimjoin it will automatically install the dependencies py_stringmatching and pandas):
In [3]:
# Import libraries
import py_stringsimjoin as ssj
import py_stringmatching as sm
import pandas as pd
import os, sys
In [4]:
print('python version: ' + sys.version)
print('py_stringsimjoin version: ' + ssj.__version__)
print('py_stringmatching version: ' + sm.__version__)
print('pandas version: ' + pd.__version__)
Joining two tables using Jaccard measure typically consists of four steps:
We begin by loading the two tables. For the purpose of this guide, we use the sample dataset that comes with the package.
In [5]:
# construct the path of the tables to be loaded. Since we are loading a
# dataset from the package, we need to access the data from the path
# where the package is installed. If you need to load your own data, you can directly
# provide your table path to the read_csv command.
table_A_path = os.sep.join([ssj.get_install_path(), 'datasets', 'data', 'person_table_A.csv'])
table_B_path = os.sep.join([ssj.get_install_path(), 'datasets', 'data', 'person_table_B.csv'])
In [6]:
# Load csv files as dataframes.
A = pd.read_csv(table_A_path)
B = pd.read_csv(table_B_path)
print('Number of records in A: ' + str(len(A)))
print('Number of records in B: ' + str(len(B)))
In [7]:
A
Out[7]:
In [8]:
B
Out[8]:
Before performing the join, we may want to profile the tables to know about the characteristics of the attributes. This can help identify:
a) unique attributes in the table which can be used as key attribute when performing the join. A key attribute is needed to uniquely identify a tuple.
b) the number of missing values present in each attribute. This can help you in deciding the attribute on which to perform the join. For example, an attribute with a lot of missing values may not be a good join attribute. Further, based on the missing value information you need to decide on how to handle missing values when performing the join (See the section below on 'Handling missing values' to know more about the options available for handling missing values when performing the join).
You can profile the attributes in a table using the following command:
In [9]:
# profile attributes in table A
ssj.profile_table_for_join(A)
Out[9]:
In [10]:
# profile attributes in table B
ssj.profile_table_for_join(B)
Out[10]:
If the input tables does not contain any key attribute, then you need to create a key attribute. In the current example, both the input tables A and B have key attributes, and hence you can proceed to the next step. In the case the table does not have any key attribute, you can add a key attribute using the following command:
In [11]:
B['new_key_attr'] = range(0, len(B))
B
Out[11]:
For the purpose of this guide, we will now join tables A and B on 'name' attribute using Jaccard measure. Next, we need to decide on what threshold to use for the join. For this guide, we will use a threshold of 0.3. Specifically, the join will now find tuple pairs from A and B such that the Jaccard score over the 'name' attributes is at least 0.3.
Since Jaccard measure treats input strings as sets of tokens, we need to select a tokenizer which can be used to tokenize each string into a set of tokens. Currently, we support tokenizers from py_stringmatching package which provides five different tokenizer types: alphabetical tokenizer, alphanumeric tokenizer, delimiter-based tokenizer, qgram tokenizer, and whitespace tokenizer.
For the purpose of this guide, we will use a whitespace tokenizer. Once we have selected a tokenizer type, we need to create a tokenizer object as shown below:
In [12]:
# create whitespace tokenizer for tokenizing 'name' attribute. The return_set flag should be set to True since
# Jaccard is a set based measure.
ws = sm.WhitespaceTokenizer(return_set=True)
# a whitespace tokenizer will tokenize the input string using whitespace
ws.tokenize('William Bridge')
Out[12]:
The next step after creating a tokenizer is to perform the join. The Jaccard join can be performed using the following command:
In [13]:
# find all pairs from A and B such that the Jaccard score
# on 'name' is at least 0.3.
# l_out_attrs and r_out_attrs denote the attributes from the
# left table (A) and right table (B) that need to be included in the output.
output_pairs = ssj.jaccard_join(A, B, 'A.id', 'B.id', 'A.name', 'B.name', ws, 0.3,
l_out_attrs=['A.name'], r_out_attrs=['B.name'])
In [14]:
len(output_pairs)
Out[14]:
In [15]:
# examine the output pairs
output_pairs
Out[15]:
By default, the pairs with empty sets of tokens are included in the output. This is because Jaccard of two empty sets is not well defined and we do not want to miss any possible matches. As you can see from the previous output, the tuple pair (a6, b7) is included in the output with a similarity score of 1. If you do not want to allow pairs containing empty sets of tokens in the output, then you need to set the allow_empty flag to False as shown below:
In [16]:
output_pairs = ssj.jaccard_join(A, B, 'A.id', 'B.id', 'A.name', 'B.name', ws, 0.3, allow_empty=False,
l_out_attrs=['A.name'], r_out_attrs=['B.name'])
In [17]:
output_pairs
Out[17]:
As you can see, the tuple pair (a6, b7) is not present in the output.
By default, pairs with missing values are not included in the output. This is because a string with a missing value can potentially match with all strings in the other table and hence the number of output pairs can become huge. If you want to include pairs with missing value in the output, you need to set the allow_missing flag to True, as shown below:
In [18]:
output_pairs = ssj.jaccard_join(A, B, 'A.id', 'B.id', 'A.name', 'B.name', ws, 0.3, allow_missing=True,
l_out_attrs=['A.name'], r_out_attrs=['B.name'])
In [19]:
output_pairs
Out[19]:
If you have multiple cores which you want to exploit for performing the join, you need to use the n_jobs option. If n_jobs is -1, all CPUs are used. If 1 is given, no parallel computing code is used at all, which is useful for debugging and is the default option. For n_jobs below -1, (n_cpus + 1 + n_jobs) are used (where n_cpus is the total number of CPUs in the machine). Thus for n_jobs = -2, all CPUs but one are used. If (n_cpus + 1 + n_jobs) becomes less than 1, then no parallel computing code will be used (i.e., equivalent to the default).
The following command exploits all the cores available to perform the join:
In [20]:
output_pairs = ssj.jaccard_join(A, B, 'A.id', 'B.id', 'A.name', 'B.name', ws, 0.3,
l_out_attrs=['A.name'], r_out_attrs=['B.name'], n_jobs=-1)
In [21]:
len(output_pairs)
Out[21]:
You need to set n_jobs to 1 when you are debugging or you do not want to use any parallel computing code. If you want to execute the join as fast as possible, you need to set n_jobs to -1 which will exploit all the CPUs in your machine. In case there are other concurrent processes running in your machine and you do not want to halt them, then you may need to set n_jobs to a value below -1.
You can find all the options available for the Jaccard join function using the help command as shown below:
In [ ]:
help(ssj.jaccard_join)
Similar to Jaccard measure, you can use the package to perform join using other measures such as cosine, Dice, edit distance, overlap and overlap coefficient. For measures such as TF-IDF which are not directly supported, you can perform the join using the filters provided in the package. To know more about other join methods as well as how to use filters, refer to the how-to guide (available from the package homepage).